"""
异步音频处理记录数据访问层
"""
from typing import Optional, List
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, desc, func, and_

from app.databases.models import AudioProcessingRecord
from app.repositories.base_async import AsyncBaseRepository


class AsyncAudioProcessingRepository(AsyncBaseRepository[AudioProcessingRecord]):
    """异步音频处理记录Repository"""
    
    def __init__(self, db: AsyncSession):
        super().__init__(AudioProcessingRecord, db)
    
    async def get_by_filename(self, filename: str) -> Optional[AudioProcessingRecord]:
        """根据文件名获取记录"""
        return await self.get_by_field("filename", filename)
    
    async def get_by_status(self, status: str) -> List[AudioProcessingRecord]:
        """根据状态获取记录列表"""
        return await self.get_multi_by_field("status", status, desc_order=True)
    
    async def get_recent_records(self, limit: int = 10) -> List[AudioProcessingRecord]:
        """获取最近的记录"""
        return await self.get_multi(
            limit=limit,
            order_by="created_at",
            desc_order=True
        )
    
    async def get_by_timeframe(
        self, 
        start_time: Optional[str] = None,
        end_time: Optional[str] = None,
        limit: int = 100
    ) -> List[AudioProcessingRecord]:
        """根据时间范围获取记录"""
        query = select(self.model)
        
        conditions = []
        if start_time:
            conditions.append(self.model.created_at >= start_time)
        if end_time:
            conditions.append(self.model.created_at <= end_time)
        
        if conditions:
            query = query.where(and_(*conditions))
        
        query = query.order_by(desc(self.model.created_at)).limit(limit)
        result = await self.db.execute(query)
        return result.scalars().all()
    
    async def update_status(
        self, 
        record_id: int, 
        status: str, 
        processing_time: Optional[float] = None,
        error_message: Optional[str] = None
    ) -> Optional[AudioProcessingRecord]:
        """更新处理状态"""
        record = await self.get(record_id)
        if record:
            update_data = {"status": status}
            if processing_time is not None:
                update_data["processing_time"] = processing_time
            if error_message is not None:
                update_data["error_message"] = error_message
            
            return await self.update(record, update_data)
        return None
    
    async def get_statistics(self) -> dict:
        """获取处理统计信息"""
        total_count = await self.count()
        success_count = await self.count({"status": "completed"})
        failed_count = await self.count({"status": "failed"})
        pending_count = await self.count({"status": "pending"})
        
        return {
            "total": total_count,
            "success": success_count,
            "failed": failed_count,
            "pending": pending_count,
            "success_rate": (success_count / total_count * 100) if total_count > 0 else 0
        }
    
    async def get_records_by_date_range(
        self,
        start_date: str,
        end_date: str,
        limit: int = 100
    ) -> List[AudioProcessingRecord]:
        """根据日期范围获取记录"""
        return await self.get_by_timeframe(start_date, end_date, limit)
    
    async def get_processing_trends(
        self, 
        days: int = 7
    ) -> List[dict]:
        """获取处理趋势数据"""
        # 这里可以实现按日期分组的统计查询
        # 示例实现，返回最近几天的记录
        records = await self.get_recent_records(limit=days * 10)
        
        # 简单的按日期分组统计
        trends = {}
        for record in records:
            date_str = record.created_at.date().isoformat()
            if date_str not in trends:
                trends[date_str] = {"total": 0, "success": 0, "failed": 0}
            
            trends[date_str]["total"] += 1
            if record.status == "completed":
                trends[date_str]["success"] += 1
            elif record.status == "failed":
                trends[date_str]["failed"] += 1
        
        return [
            {
                "date": date,
                "total": data["total"],
                "success": data["success"],
                "failed": data["failed"],
                "success_rate": (data["success"] / data["total"] * 100) if data["total"] > 0 else 0
            }
            for date, data in sorted(trends.items())
        ]
    
    async def get_top_files_by_size(self, limit: int = 10) -> List[AudioProcessingRecord]:
        """获取文件大小最大的记录"""
        query = select(self.model).order_by(desc(self.model.file_size)).limit(limit)
        result = await self.db.execute(query)
        return result.scalars().all()
    
    async def get_average_processing_time(self) -> Optional[float]:
        """获取平均处理时间"""
        query = select(func.avg(self.model.processing_time)).where(
            self.model.processing_time.isnot(None)
        )
        result = await self.db.execute(query)
        return result.scalar()
    
    async def cleanup_old_records(self, days: int = 30) -> int:
        """清理旧记录"""
        from datetime import datetime, timedelta
        cutoff_date = datetime.now() - timedelta(days=days)
        
        query = select(func.count(self.model.id)).where(
            self.model.created_at < cutoff_date
        )
        result = await self.db.execute(query)
        count = result.scalar()
        
        if count > 0:
            delete_query = select(self.model).where(
                self.model.created_at < cutoff_date
            )
            delete_result = await self.db.execute(delete_query)
            records_to_delete = delete_result.scalars().all()
            
            for record in records_to_delete:
                await self.db.delete(record)
            
            await self.db.commit()
        
        return count
